<?php
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * EXPORTING DATABAE
 *
 * @author Samuel Pulta
 */
class Exporting_Database extends Database{
    
    /*  ===========================================
        =====  Get / Show Database List =====
        =========================================== */
            public function database_list(){
                $ret = "";
                $query = $this->query("SHOW DATABASES","array");
                
                foreach($query as $key=>$val){
                    $database_name = $query[$key]['Database'];
                    if($key != "0"){
                        $ret .= "<option value='" . $key . "'>" . $database_name . "</option>";
                    }
                }
                return $ret;
            }
    
    /*  ===========================================
        =====  export_download_selected_table =====
        =========================================== */
        /*
         * Create an export database base on selected table
         * @db_name - chosen database
         * @selected_tables - list of selected tables
         * @folder_location - path where the exported files will be move
         *
         */
            public function export_download_selected_table($db_name,$selected_tables = array(),$folder_location){
                // Establish variables and setup:
                $db_name = $db_name;
                
                // Backup directory:
                $dir = DIR_DUMP_LOC . $db_name;
                
                // Make the database-specific directory, if it doesn't exist.
                if (!is_dir($dir)) {
                    if (!@mkdir($dir)) {
                        die ("<p>The backup directory--$dir--could not be created.</p>");
                    }
                }
                
                // Get the current time for using in all filenames:
                $bu_time = time();
                
                // Connect to the database:
                $dbc = @mysqli_connect (DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, $db_name);
            
                    
                    // Fetch each table name.
                    for($i = 0; $i < count($selected_tables); $i++){
                        
                        $table = $selected_tables[$i];
                        
                        // Get the records for this table:
                        $q2 = "SELECT * FROM $table";
                        $r2 = mysqli_query($dbc, $q2);
                        
                        // Back up if records exist:
                        if (mysqli_num_rows($r2) > 0) {
                                
                                // Attempt to open the file:
                                if ($fp = fopen ("$dir/{$db_name}_{$table}_{$bu_time}.txt", 'w9')) {
                                    fwrite ($fp, '--' . "\n");
                                    fwrite ($fp, '-- Database: `' . $db_name . '`' . "\n");
                                    fwrite ($fp, '--' . "\n");
                                    fwrite ($fp, 'CREATE DATABASE IF NOT EXISTS `' . $db_name . '` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci' . "\n");
                                    fwrite ($fp, 'USE `' . $db_name . '`' . "\n");
                                    
                                    // Show list of column per table selected
                                    $q3 = "SHOW COLUMNS FROM $table";
                                    $result = mysqli_query($dbc, $q3);
                                    $fields = "";
                                    $rec = "";
                                        fwrite ($fp, '-----------------------------------------------------------' . "\n");
                                        fwrite ($fp, '--' . "\n");
                                        fwrite ($fp, '-- Table structure for table ' . $table . "\n");
                                        fwrite ($fp, '--' . "\n");
                                        fwrite ($fp, 'CREATE TABLE IF NOT EXISTS `' . $table . '` (' . "\n");
                                        
                                            $count_row_num = mysqli_num_rows($result) - 1;
                                            foreach($result as $key=>$value){
                                                $fields .= '`';
                                                $fields .= $value['Field'];
                                                $fields .= '`,';
                                                
                                                //if($count_row_num == $key){
                                                //    fwrite ($fp, '`' . $value['Field'] . '` ' . $value['Type'] . ' NOT NULL ' . $value['Extra'] . "\n");
                                                //}else{
                                                    fwrite ($fp, '`' . $value['Field'] . '` ' . $value['Type'] . ' NOT NULL ' . $value['Extra'] . ',' . "\n");
                                                //}
                                                
                                            }
                                            
                                            $count_not_null = 0;
                                            $count_null = 0;
                                            
                                            // count null elements in the array
                                            foreach($result as $key=>$value){
                                                if($value['Key'] == ""){
                                                    $count_null++;
                                                }
                                            }
                                            // Properties key of each record inserted to the db
                                            foreach($result as $key=>$value){
                                                if($value['Key'] != ""){
                                                    if($value['Key'] == "PRI"){
                                                        $value_key = "PRIMARY KEY (`" . $value['Field'] . "`)";
                                                    }elseif($value['Key'] == "UNI"){
                                                        $value_key = "UNIQUE KEY `" . $value['Field'] . "_UNIQUE` (`" . $value['Field'] . "`)";
                                                    }elseif($value['Key'] == "MUL"){
                                                        $value_key = "KEY `" . $value['Field'] . "` (`" . $value['Field'] . "`)";
                                                    }else{
                                                        $value_key = "";
                                                    }
                                                    
                                                    $count_not_null++;
                                                    $total_query = $count_row_num - $count_null;
                                                    $total_not_null_query = $count_not_null - 1;
                                                    
                                                    if( $total_query == $total_not_null_query ){
                                                        fwrite ($fp, $value_key . "\n");
                                                    }else{
                                                        fwrite ($fp, $value_key . ",\n");
                                                    }
                                                }
                                            }
                                            
                                        fwrite ($fp, ') ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;' . "\n");
                                        
                                        fwrite ($fp, '-- --------------------------------------------------------' . "\n");
                                        
                                        fwrite ($fp, '--' . "\n");
                                        fwrite ($fp, '-- Dumping data for table `' . $table . '`' . "\n");
                                        fwrite ($fp, '--' . "\n");
                                    
                                        fwrite ($fp, '-- --------------------------------------------------------' . "\n");
                                        
                                        fwrite ($fp, 'INSERT INTO `' . $table . '` (' . substr($fields, 0, -1) . ') VALUES' . "\n");
                                        $count = 1;
                                        
                                        // Fetch all the records for this table:
                                        while ($row = mysqli_fetch_array($r2, MYSQLI_NUM)) {
                                                // Write the data as a comma-delineated row:
                                                fwrite ($fp, "(");
                                                
                                                foreach ($row as $val => $key) {
                                                    //$rec = "'$count'," . "'$value'";
                                                    if(count($row) - 1 == $val){
                                                        fwrite ($fp, "'$key' ");
                                                    }else{
                                                        fwrite ($fp, "'$key', ");
                                                    }
                                                    
                                                }
                                                if($count == mysqli_num_rows($r2)){
                                                    fwrite ($fp, ");");    
                                                }else{
                                                    fwrite ($fp, "),"); 
                                                }
                                                $count++;
                                                // Add a new line to each row:
                                                fwrite ($fp, "\n"); 
                                        } // End of WHILE loop.
                                        
                                        // Close the file:
                                        fclose ($fp); 
                                        
                                        // Print the success:
                                        //echo "<p>Table '$table' backed up.</p>\n";
                                        
                                } else { // Could not create the file!
                                        return "<p>The file--$dir/{$table}_{$time}.sql.gz--could not be opened for writing.</p>\n";
                                        break; // Leave the WHILE loop.
                                } // End of gzopen() IF.
                                
                        } // End of mysqli_num_rows() IF.
                    } // End of For loop.
                    
                    // Indicate what is happening:
                    return "Selected database was successfully exported.";
                    
            }
            
            
            public function tester(){
                //$db_name = "formalistic_mobility";
                //$dbc = @mysqli_connect (DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, $db_name);
                //
                //$q3 = "SHOW COLUMNS FROM company";
                //$result = mysqli_query($dbc, $q3);
                //$count_not_null = 0;
                //$count_null = 0;
                //$count_row_num = mysqli_num_rows($result) - 1;
                //
                //foreach($result as $key=>$value){
                //    if($value['Key'] == ""){
                //        $count_null++;
                //    }
                //}
                //
                //foreach($result as $key=>$value){
                //    if($value['Key'] != ""){
                //        echo "1--- " . $value['Key'];
                //        
                //        $count_not_null++;
                //        $total_query = $count_row_num - $count_null;
                //        $total_not_null_query = $count_not_null - 1;
                //        if( $total_query == $total_not_null_query ){
                //           echo $value['Field'];
                //        }else{
                //            echo $value['Field'] . ",";
                //        }
                //    }else{
                //        $count_null++;
                //    }
                //    echo "<br>";
                //    //$length  = array_count_values($result);
                //    
                //    //print_r($value);
                //    //print_r("<br>");
                //}
                //
                //
                ////echo $count_not_null;
                ////echo "<br>";
                ////echo $count_null;
            }
    
    
}
